﻿ DECLARE @DATECOUNT DATETIME = '20160101'
 PRINT @DATECOUNT
 DECLARE @STRDATECOUNT NVARCHAR(10)

 WHILE @DATECOUNT <= '20161231'
 BEGIN
 PRINT CONVERT(NVARCHAR(10),@DATECOUNT,121)
 set @STRDATECOUNT =  CONVERT(NVARCHAR(10), @DATECOUNT, 121)

 select top 1 * from S16_FT_HIST_2016 where id = 'FT16004CLDM00009'

 --INSERT S16_FT_HIST_2016
 --SELECT * FROM SERVER16.VPB_WHR2.DBO.FT_HIST
 --WHERE BUSINESS_DATE = @STRDATECOUNT
  
INSERT FT_HIST_2016_FILTER_ACCOUNT
SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG	
FROM S16_FT_HIST_2016
	WHERE BUSINESS_DATE = @STRDATECOUNT AND (
			(DEBIT_ACCOUNT IN (	SELECT ACCTNO  FROM S74_DPTB_MASTER
							WHERE BI_SEGMENT = 'SME' )) 
						OR 
			(CREDIT_ACCOUNT IN (	SELECT ACCTNO  FROM S74_DPTB_MASTER
							WHERE BI_SEGMENT = 'SME' ))
			)
			   

 SET @DATECOUNT = @DATECOUNT + 1
 END


 select * from FT_HIST_2016_FILTER_ACCOUNT


 
 ALTER TABLE FT_HIST_2016_FILTER_ACCOUNT
 ADD CIF_DEBIT VARCHAR(20),CIF_CREDIT VARCHAR(20),CHECK_CIF	VARCHAR(4), AMOUNT FLOAT, AMOUNT_QD FLOAT, TYGIA FLOAT, CIF VARCHAR(30)

 -- UPDATE CIF_DEBIT
UPDATE FT_HIST_2016_FILTER_ACCOUNT
SET CIF_DEBIT=B.CIF
FROM FT_HIST_2016_FILTER_ACCOUNT A,S74_DPTB_MASTER B
WHERE A.DEBIT_ACCOUNT= B.ACCTNO


 -- UPDATE CIF_DEBIT
UPDATE FT_HIST_2016_FILTER_ACCOUNT
SET CIF_CREDIT=B.CIF
FROM FT_HIST_2016_FILTER_ACCOUNT A, S74_DPTB_MASTER B
WHERE A.CREDIT_ACCOUNT= B.ACCTNO

  -- UPDATE CHECK_CIF	 ( SELECT * FROM FT_HIST_2016_FILTER_ACCOUNT WHERE CIF IS NULL )

UPDATE FT_HIST_2016_FILTER_ACCOUNT
SET CHECK_CIF = CASE WHEN CIF_DEBIT = CIF_CREDIT THEN '0' ELSE '1' END

-- UPDATE CIF
   UPDATE FT_HIST_2016_FILTER_ACCOUNT
   SET CIF = CIF_CREDIT

   UPDATE FT_HIST_2016_FILTER_ACCOUNT
   SET CIF = CIF_DEBIT
   WHERE CIF IS NULL

 -- UPDATE AMOUNT

 --select max from FT_HIST_2016_FILTER_ACCOUNT


 UPDATE FT_HIST_2016_FILTER_ACCOUNT
 SET AMOUNT = CASE	WHEN CREDIT_AMOUNT NOT LIKE '%Ý%' THEN CREDIT_AMOUNT ELSE DEBIT_AMOUNT END

 select * from FT_HIST_2016_FILTER_ACCOUNT
 where CREDIT_AMOUNT <> DEBIT_AMOUNT

 -- UPDATE TỶ GIÁ

 UPDATE FT_HIST_2016_FILTER_ACCOUNT
 SET TYGIA = B.MID_RATE
 FROM FT_HIST_2016_FILTER_ACCOUNT A, (SELECT * FROM SERVER16.VPB_WHR2.DBO.FOCURR_SAVE) B
 WHERE A.CREDIT_CCY =B.CODE and a.BUSINESS_DATE = b.save_date

  --- UPDATE AMOUNT_QD

  UPDATE FT_HIST_2016_FILTER_ACCOUNT
  SET AMOUNT_QD = AMOUNT*TYGIA



  

SELECT BUSINESS_DATE,ID,TRANSACTION_CODE AS TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG FROM AD_HOC.DBO.TT_HIST_201511 -----CẢ THÁNG, SERVER 16 


 
   --- XỬ LÝ BẢNG TT_HIST

--   SELECT BUSINESS_DATE,ID,TRANSACTION_CODE AS TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
--,DEBIT_CATEG INTO S16_TT_HIST_2016_FILTER_ACCOUNT FROM S16_TT_HIST_2016

   DECLARE @DATECOUNT1 DATETIME = '20160101'
 PRINT @DATECOUNT1
 DELETE FROM S16_TT_HIST_2016
 WHILE @DATECOUNT1 <= '20161231'
 BEGIN
 PRINT CONVERT(NVARCHAR(10),@DATECOUNT1,121)
 DECLARE @STRDATECOUNT1 NVARCHAR(10) =  CONVERT(NVARCHAR(10), @DATECOUNT1, 121)

 --INSERT S16_TT_HIST_2016
 SELECT * INTO TT_NGAY_TAM
 FROM SERVER16.VPB_WHR2.DBO.TT_HIST
 WHERE BUSINESS_DATE = @STRDATECOUNT1
  
INSERT S16_TT_HIST_2016_FILTER_ACCOUNT
SELECT BUSINESS_DATE,ID,TRANSACTION_CODE AS TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG 	
FROM TT_NGAY_TAM
	WHERE ((DEBIT_ACCOUNT IN (	SELECT ACCTNO  FROM S74_DPTB_MASTER)) 
						OR 
			(CREDIT_ACCOUNT IN (SELECT ACCTNO  FROM S74_DPTB_MASTER)))

			 DROP TABLE TT_NGAY_TAM
 SET @DATECOUNT1 = @DATECOUNT1 + 1
 END

--SELECT DISTINCT CREDIT_CCY FROM FT_KSSV_201511 WHERE  CIF_DEBIT IS NULL AND CIF_CREDIT IS NULL
 -- UPDATE CIF_DEBIT VÀ CIF_CREDIT

 ALTER TABLE S16_TT_HIST_2016_FILTER_ACCOUNT
 ADD CIF_DEBIT VARCHAR(20),CIF_CREDIT VARCHAR(20),CHECK_CIF	VARCHAR(4), AMOUNT FLOAT, AMOUNT_QD FLOAT, TYGIA FLOAT,CIF VARCHAR(30)


 -- UPDATE CIF_DEBIT
UPDATE S16_TT_HIST_2016_FILTER_ACCOUNT
SET CIF_DEBIT=B.CIF
FROM S16_TT_HIST_2016_FILTER_ACCOUNT A, S74_DPTB_MASTER B
WHERE A.DEBIT_ACCOUNT= B.ACCTNO


 -- UPDATE CIF_DEBIT
UPDATE S16_TT_HIST_2016_FILTER_ACCOUNT
SET CIF_CREDIT=B.CIF
FROM S16_TT_HIST_2016_FILTER_ACCOUNT A, S74_DPTB_MASTER B
WHERE A.CREDIT_ACCOUNT= B.ACCTNO

  -- UPDATE CHECK_CIF

UPDATE S16_TT_HIST_2016_FILTER_ACCOUNT
SET CHECK_CIF = CASE WHEN CIF_DEBIT = CIF_CREDIT THEN '0' ELSE '1' END

 -- UPDATE CIF
   UPDATE S16_TT_HIST_2016_FILTER_ACCOUNT
   SET CIF = CIF_CREDIT

   UPDATE S16_TT_HIST_2016_FILTER_ACCOUNT
   SET CIF = CIF_DEBIT
   WHERE CIF IS NULL


 -- UPDATE AMOUNT
	   
	   -- SELECT * FROM S16_TT_HIST_2016_FILTER_ACCOUNT WHERE DEBIT_AMOUNT LIKE N'%Ý%'

 UPDATE S16_TT_HIST_2016_FILTER_ACCOUNT
 SET AMOUNT = CASE	WHEN ISNUMERIC(CREDIT_AMOUNT) = 1 THEN CREDIT_AMOUNT 
					ELSE DEBIT_AMOUNT END 


 

 -- UPDATE TỶ GIÁ

 UPDATE A
 SET A.TYGIA = B.MID_RATE
 FROM S16_TT_HIST_2016_FILTER_ACCOUNT A, (SELECT * FROM SERVER16.VPB_WHR2.DBO.FOCURR_SAVE) B
 WHERE A.CREDIT_CCY =B.CODE AND A.BUSINESS_DATE = B.SAVE_DATE


  --- UPDATE AMOUNT_QD

  UPDATE S16_TT_HIST_2016_FILTER_ACCOUNT
  SET AMOUNT_QD = AMOUNT*TYGIA




  
---- GHÉP 2 BẢNG TT_KSSV_201511 VÀ FT_KSSV_201511 ( LOẠI NHỮNG TRƯỜNG HỢP CÙNG CHUNG 1 CIF KH)
						 --DROP TABLE FT_TT_KSSV_201511	   -- SELECT * FROM  FT_TT_KSSV_201511_1
SELECT * 
INTO FT_TT_merge_2016_1
FROM 
 (
 SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF 
 FROM S16_TT_HIST_2016_FILTER_ACCOUNT
 WHERE CHECK_CIF = '1' 
 UNION ALL
  SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF  
  FROM FT_HIST_2016_FILTER_ACCOUNT
 WHERE CHECK_CIF = '1'
) X

------ TRƯỜNG HỢP CHUNG CIF		 SELECT * FROM 	 FT_TT_KSSV_201511_0
SELECT * 
INTO FT_TT_merge_2016_0
FROM 
 (
 SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF 
 FROM S16_TT_HIST_2016_FILTER_ACCOUNT
 WHERE CHECK_CIF <> '1' 
 UNION ALL
  SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF  
  FROM FT_HIST_2016_FILTER_ACCOUNT
 WHERE CHECK_CIF <> '1'
) X


------------------ TRẢ NỢ GỐC-LẠI KHÁCH HÀNG	DROP TABLE DONG_TIEN_GHI_CO_201511	 -- SELECT * FROM FT_TT_KSSV_201511 WHERE 
 
SELECT A.CIF,B.CUS_NAME,B.BRANCH_ID,C.BRANCH_NAME_SME,C.REGION,SUM(A.AMOUNT_QD) AS AMOUNT 
INTO DONG_TIEN_GHI_CO_2016
FROM FT_TT_merge_2016_1	A
LEFT JOIN TBL_CUSTOMER B
ON A.CIF = B.CIF COLLATE DATABASE_DEFAULT
LEFT JOIN TBL_BRANCH C
ON B.BRANCH_ID=C.BRANCH_ID
GROUP BY A.CIF,B.CUS_NAME,B.BRANCH_ID,C.BRANCH_NAME_SME,C.REGION 

  ALTER TABLE DONG_TIEN_GHI_CO_2016
  ADD TRA_NO FLOAT


  select * from DONG_TIEN_GHI_CO_2016 where CUS_NAME is null
select * from SERVER74.BICDATA.DBO.CUSTOMER where recid = '2665220'										
		 --select * 
		 --into s74_FT_TT_merge_2016_repayed_2016
		 --from SERVER74.STAGING.DBO.LNTB_LDPD_REPAYED
		 --where REPAY_DATE BETWEEN '20160101' AND '20161231'					------- THAY NGÀY TRẢ NỢ
			--				AND CIF IN (SELECT CIF COLLATE DATABASE_DEFAULT FROM FT_TT_merge_2016_1)

UPDATE DONG_TIEN_GHI_CO_2016
SET TRA_NO = B.REPAY_AMOUNT
FROM DONG_TIEN_GHI_CO_2016 A,  (SELECT  CIF, SUM(REPAY_AMOUNT) AS REPAY_AMOUNT  
							FROM s74_FT_TT_merge_2016_repayed_2016
							GROUP BY CIF  ) B
WHERE A.CIF=B.CIF COLLATE DATABASE_DEFAULT

  ALTER TABLE DONG_TIEN_GHI_CO_2016
  ADD DOANH_THU_GHI_CO	FLOAT

  UPDATE DONG_TIEN_GHI_CO_2016
  SET DOANH_THU_GHI_CO = ISNULL(AMOUNT,0) - ISNULL(TRA_NO,0) 
 

 SELECT * FROM 	DONG_TIEN_GHI_CO_2016
 WHERE DOANH_THU_GHI_CO <0

